<!doctype html>
<html>
<head>
<meta charset='UTF-8'><meta name='viewport' content='width=device-width initial-scale=1'>
<title>10-22 子查询</title>
</head>
<body><p>&nbsp;</p>
<h3 id='数据'>数据</h3>
<pre><code class='language-sql' lang='sql'>CREATE TABLE 部门 (
    部门编号 INT PRIMARY KEY,
    部门名称 VARCHAR(50) NOT NULL
);

CREATE TABLE 员工 (
    员工编号 INT PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    部门编号 INT,
    工资 DECIMAL(10,2),
    FOREIGN KEY (部门编号) REFERENCES 部门(部门编号)
);

INSERT INTO 部门 VALUES (1, &#39;销售部&#39;);
INSERT INTO 部门 VALUES (2, &#39;工程部&#39;);
INSERT INTO 部门 VALUES (3, &#39;人力资源部&#39;);

INSERT INTO 员工 VALUES (1, &#39;李华&#39;, 1, 50000);
INSERT INTO 员工 VALUES (2, &#39;王明&#39;, 2, 60000);
INSERT INTO 员工 VALUES (3, &#39;张强&#39;, 1, 55000);
INSERT INTO 员工 VALUES (4, &#39;赵丽&#39;, 3, 52000);
INSERT INTO 员工 VALUES (5, &#39;孙静&#39;, NULL, 48000);  -- 没有部门编号
</code></pre>
<h3 id='sql-子查询示例'>SQL 子查询示例</h3>
<h4 id='1带in关键字的子查询'>（1）带IN关键字的子查询</h4>
<p>找出所有属于销售部的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 部门编号 IN (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;销售部&#39;);
</code></pre>
<p>找出所有属于工程部或者人力资源部的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 部门编号 IN (SELECT 部门编号 FROM 部门 WHERE 部门名称 IN (&#39;工程部&#39;, &#39;人力资源部&#39;));
</code></pre>
<p>找出所有不属于销售部的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 部门编号 NOT IN (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;销售部&#39;);
</code></pre>
<h4 id='2带exists关键字的子查询'>（2）带EXISTS关键字的子查询</h4>
<p>找出有员工的部门。</p>
<pre><code class='language-sql' lang='sql'>SELECT d.部门编号, d.部门名称 FROM 部门 d WHERE EXISTS (SELECT 1 FROM 员工 e WHERE e.部门编号 = d.部门编号);
</code></pre>
<p>找出没有员工的部门。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 部门 WHERE NOT EXISTS (SELECT 1 FROM 员工 WHERE 员工.部门编号 = 部门.部门编号);
</code></pre>
<p>找出有超过两位员工的部门。</p>
<pre><code class='language-sql' lang='sql'>SELECT d.部门编号, d.部门名称 FROM 部门 d WHERE EXISTS (SELECT COUNT(*) FROM 员工 e WHERE e.部门编号 = d.部门编号 GROUP BY e.部门编号 HAVING COUNT(*) &gt; 2);
</code></pre>
<h4 id='3带any或some关键字的子查询'>（3）带ANY（或SOME）关键字的子查询</h4>
<p>找出工资高于任何销售部员工的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &gt; ANY (SELECT 工资 FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;销售部&#39;));
</code></pre>
<p>找出工资低于任何人力资源部员工的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &lt; ANY (SELECT 工资 FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;人力资源部&#39;));
</code></pre>
<p>找出工资等于任意工程部员工的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 = ANY (SELECT 工资 FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;工程部&#39;));
</code></pre>
<h4 id='4带all关键字的子查询'>（4）带ALL关键字的子查询</h4>
<p>找出工资高于所有销售部员工的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &gt; ALL (SELECT 工资 FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;销售部&#39;));
</code></pre>
<p>找出工资低于所有人力资源部员工的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &lt; ALL (SELECT 工资 FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;人力资源部&#39;));
</code></pre>
<p>找出工资等于所有工程部员工的最低工资的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 = ALL (SELECT 工资 FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;工程部&#39;)) AND 工资 = (SELECT MIN(工资) FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;工程部&#39;));
</code></pre>
<h4 id='5带比较运算符的子查询'>（5）带比较运算符的子查询</h4>
<p>找出工资低于工程部平均工资的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &lt; (SELECT AVG(工资) FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;工程部&#39;));
</code></pre>
<p>找出工资高于销售部平均工资的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &gt; (SELECT AVG(工资) FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;销售部&#39;));
</code></pre>
<p>找出工资等于人力资源部最高工资的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 = (SELECT MAX(工资) FROM 员工 WHERE 部门编号 = (SELECT 部门编号 FROM 部门 WHERE 部门名称 = &#39;人力资源部&#39;));
</code></pre>
<h4 id='6子查询的其他应用'>（6）子查询的其他应用</h4>
<p>找出没有分配到任何部门的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 部门编号 IS NULL;
</code></pre>
<p>找出工资高于所有员工平均工资的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 工资 &gt; (SELECT AVG(工资) FROM 员工);
</code></pre>
<p>找出部门编号为1的所有员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工 WHERE 部门编号 = 1;
</code></pre>
<h4 id='7union合并查询'>（7）UNION合并查询</h4>
<p>找出所有的员工和部门信息，包括没有员工的部门。</p>
<pre><code class='language-sql' lang='sql'>SELECT &#39;员工&#39; AS 记录类型, 员工编号, 姓名, 部门编号 FROM 员工
UNION ALL
SELECT &#39;部门&#39;, NULL, 部门名称, 部门编号 FROM 部门 WHERE 部门编号 NOT IN (SELECT 部门编号 FROM 员工);
</code></pre>
<p>找出所有员工的姓名和部门名称。</p>
<pre><code class='language-sql' lang='sql'>SELECT &#39;员工&#39; AS 记录类型, 姓名, 部门名称 FROM 员工 e JOIN 部门 d ON e.部门编号 = d.部门编号
UNION ALL
SELECT &#39;无部门&#39;, 姓名, NULL FROM 员工 WHERE 部门编号 IS NULL;
</code></pre>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<h2 id='练习'><u>练习</u></h2>
<h3 id='项目背景'>项目背景</h3>
<p>假设你是一家公司的数据库管理员，你需要从公司数据库中提取一些关键信息以帮助管理层做出决策。数据库中有两个主要表：<code>部门</code> 和 <code>员工</code>。</p>
<h3 id='数据库结构'>数据库结构</h3>
<ul>
<li><p><code>部门</code> 表：</p>
<ul>
<li><code>部门编号 INT</code>（主键）</li>
<li><code>部门名称 VARCHAR(50)</code>（非空）</li>

</ul>
</li>
<li><p><code>员工</code> 表：</p>
<ul>
<li><code>员工编号 INT</code>（主键）</li>
<li><code>姓名 VARCHAR(50)</code>（非空）</li>
<li><code>部门编号 INT</code>（外键）</li>
<li><code>工资 DECIMAL(10,2)</code></li>

</ul>
</li>

</ul>
<h3 id='示例数据'>示例数据</h3>
<pre><code class='language-sql' lang='sql'>CREATE TABLE 部门 (
    部门编号 INT PRIMARY KEY,
    部门名称 VARCHAR(50) NOT NULL
);

CREATE TABLE 员工 (
    员工编号 INT PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    部门编号 INT,
    工资 DECIMAL(10,2),
    FOREIGN KEY (部门编号) REFERENCES 部门(部门编号)
);

INSERT INTO 部门 VALUES (1, &#39;销售部&#39;);
INSERT INTO 部门 VALUES (2, &#39;工程部&#39;);
INSERT INTO 部门 VALUES (3, &#39;人力资源部&#39;);

INSERT INTO 员工 VALUES (1, &#39;李华&#39;, 1, 50000);
INSERT INTO 员工 VALUES (2, &#39;王明&#39;, 2, 60000);
INSERT INTO 员工 VALUES (3, &#39;张强&#39;, 1, 55000);
INSERT INTO 员工 VALUES (4, &#39;赵丽&#39;, 3, 52000);
INSERT INTO 员工 VALUES (5, &#39;孙静&#39;, NULL, 48000);
</code></pre>
<h3 id='课堂小项目任务'>课堂小项目任务</h3>
<h4 id='任务-1部门员工统计'>任务 1：部门员工统计</h4>
<p>编写一个SQL查询，找出每个部门的员工数量。</p>
<pre><code class='language-sql' lang='sql'>SELECT d.部门名称, COUNT(e.员工编号) AS 员工数
FROM 部门 d
LEFT JOIN 员工 e ON d.部门编号 = e.部门编号
GROUP BY d.部门编号;
</code></pre>
<h4 id='任务-2高薪员工'>任务 2：高薪员工</h4>
<p>编写一个SQL查询，找出工资高于所有部门平均工资的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工
WHERE 工资 &gt; (SELECT AVG(工资) FROM 员工);
</code></pre>
<h4 id='任务-3部门平均工资'>任务 3：部门平均工资</h4>
<p>编写一个SQL查询，找出每个部门的平均工资。</p>
<pre><code class='language-sql' lang='sql'>SELECT d.部门名称, AVG(e.工资) AS 平均工资
FROM 部门 d
JOIN 员工 e ON d.部门编号 = e.部门编号
GROUP BY d.部门编号;
</code></pre>
<h4 id='任务-4无部门员工'>任务 4：无部门员工</h4>
<p>编写一个SQL查询，找出所有没有分配到任何部门的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT * FROM 员工
WHERE 部门编号 IS NULL;
</code></pre>
<h4 id='任务-5部门最高工资'>任务 5：部门最高工资</h4>
<p>编写一个SQL查询，找出每个部门的最高工资及对应的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT d.部门名称, e.姓名, e.工资
FROM 部门 d
JOIN 员工 e ON d.部门编号 = e.部门编号
WHERE (d.部门编号, e.工资) IN (SELECT 部门编号, MAX(工资) FROM 员工 GROUP BY 部门编号);
</code></pre>
<h4 id='任务-6部门最低工资'>任务 6：部门最低工资</h4>
<p>编写一个SQL查询，找出每个部门的最低工资及对应的员工。</p>
<pre><code class='language-sql' lang='sql'>SELECT d.部门名称, e.姓名, e.工资
FROM 部门 d
JOIN 员工 e ON d.部门编号 = e.部门编号
WHERE (d.部门编号, e.工资) IN (SELECT 部门编号, MIN(工资) FROM 员工 GROUP BY 部门编号);
</code></pre>
<h4 id='任务-7所有员工和部门信息'>任务 7：所有员工和部门信息</h4>
<p>编写一个SQL查询，找出所有员工和部门的信息，包括没有员工的部门。</p>
<pre><code class='language-sql' lang='sql'>SELECT &#39;员工&#39; AS 记录类型, 员工编号, 姓名, 部门编号 FROM 员工
UNION ALL
SELECT &#39;部门&#39;, NULL, 部门名称, 部门编号 FROM 部门 WHERE 部门编号 NOT IN (SELECT 部门编号 FROM 员工);
</code></pre>
<h3 id='项目评估'>项目评估</h3>
<ul>
<li><p><strong>提交要求</strong>：需要提交他们编写的SQL查询，并附上运行结果截图。</p>
</li>
<li><p><strong>评估标准</strong>：</p>
<ul>
<li>查询是否正确执行并返回预期结果。</li>
<li>查询逻辑是否清晰且易于理解。</li>
<li>是否使用了适当的子查询和合并查询技巧。</li>

</ul>
</li>

</ul>
<p>&nbsp;</p>
</body>
</html>